﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Threading.Tasks;
using Jinjia.Approve.Entity;
using Jinjia.Approve.Entity.Entity;
using Jinjia.Approve.Entity.Entity.JJFlow;
using Jinjia.Approve.IRepository.JJFlow;
using Jinjia.Approve.Model.Input.JJFlow;
using Jinjia.Approve.Model.VM.JJFlow.DTO;
using Jinjia.Approve.Model.VM.JJFlow.Request;
using Jinjia.Core.BaseInfrastructure.StorageModel;
using Jinjia.Core.BaseInfrastructure.StorageModel.Model;
using Jinjia.Core.UnitOfWork.UnitOfWorkComponent;
using MySql.Data.MySqlClient;
using SqlSugar;

namespace Jinjia.Approve.Repository.JJFlow
{
    public class FlowInstanceEmpRepository : IFlowInstanceEmpRepository
    {
        private readonly ISqlSugarClient _db;
        private readonly IInitUserInfo _initUserInfo;

        public FlowInstanceEmpRepository(IUnitOfWork unitOfWork,
            IInitUserInfo initUserInfo)
        {
            _db = unitOfWork.GetDbClient();
            _initUserInfo = initUserInfo;
        }

        public async Task<List<EmpInfo>> GetInstanceNodeEmp(int node_id, int instance_id, int template_id)
        {
            var user_list = await _db.Queryable<jj_flow_instance_emp, erpa_user_tbl>((a, b) => new JoinQueryInfos(JoinType.Left, a.user_id == b.id))
                .Where(a => a.node_id == node_id && a.instance_id == instance_id)
                .Select<EmpInfo>("a.user_id,b.user_name,a.node_id,a.is_complete ").ToListAsync();
            return user_list;
        }

        public async Task<List<EmpInfo>> GetInstanceNodeEmp(int node_id, int instance_id, int template_id, int is_complete)
        {
            var query = _db.Queryable<jj_flow_instance_emp, erpa_user_tbl>((a, b) => new JoinQueryInfos(JoinType.Left, a.user_id == b.id));

            if (node_id != 0)
                query.Where(a => a.node_id == node_id);
            query.Where(a => a.instance_id == instance_id && a.is_complete == is_complete);

            var user_list = await query.Select<EmpInfo>("a.user_id,b.user_name,a.node_id,a.is_complete ").ToListAsync();
            return user_list;
        }

        public async Task<List<EmpInfo>> GetInstanceUserInfo(int instance_id, int template_id)
        {
            var user_list = await _db.Queryable<jj_flow_instance_emp, erpa_user_tbl>((a, b) => new JoinQueryInfos(JoinType.Left, a.user_id == b.id))
                .Where(a => a.instance_id == instance_id && a.template_id == template_id)
                .Select<EmpInfo>("a.id,a.node_id,a.user_id,b.user_name,b.union_id,a.is_complete,a.source_emp_id,a.update_time")
                .ToListAsync();
            return user_list;
        }

        public async Task<List<jj_flow_instance_emp>> GetList(int instanceId, int nodeId)
        {
            return await _db.Queryable<jj_flow_instance_emp>()
                .Where(m => m.instance_id == instanceId && m.node_id == nodeId)
                .ToListAsync();
        }

        public async Task<List<InstanceEmpDTO>> GetApprovalPendingList(int userId, List<int> instanceIds, List<int> instanceEmpIds)
        {
            return await _db.Queryable<jj_flow_instance_emp>()
                .InnerJoin<jj_flow_instance>((a, b) => a.instance_id == b.id)
                .Where((a, b) => a.is_complete == 0 && b.status == "0")
                .WhereIF(userId > 0, a => a.user_id == userId)
                .WhereIF(instanceIds != null && instanceIds.Count > 0, a => instanceIds.Contains(a.instance_id))
                .WhereIF(instanceEmpIds != null && instanceEmpIds.Count > 0, a => instanceEmpIds.Contains(a.id))
                .Select<InstanceEmpDTO>("a.*,b.current_node_id")
                .ToListAsync();
        }

        public async Task Save(jj_flow_instance_emp data)
        {
            if (data.id <= 0)
            {
                await _db.Insertable(data).ExecuteCommandAsync();
            }
            else
            {
                await _db.Updateable(data).ExecuteCommandAsync();
            }
        }

        public async Task Save(List<jj_flow_instance_emp> data)
        {
            var insertData = data.Where(x => x.id <= 0).ToList();

            if (insertData.Any())
            {
                await _db.Insertable(insertData).ExecuteCommandAsync();
            }

            var updateData = data.Where(x => x.id > 0).ToList();
            if (updateData.Any())
            {
                await _db.Updateable(updateData).ExecuteCommandAsync();
            }
        }

        public async Task<bool> CheckNodeUser(int node_id, int template_id, int user_id, int instance_id)
        {
            var list = await _db.Queryable<jj_flow_instance_emp>()
                .Where(x => x.node_id == node_id && x.template_id == template_id && x.user_id == user_id && x.instance_id == instance_id)
                .ToListAsync();

            if (list == null || list.Count == 0)
            {
                return false;
            }

            return true;
        }

        public async Task<bool> CheckNodeTrack(int node_id, int user_id, int instance_id)
        {
            var track_info = await _db.Queryable<jj_flow_instance_emp>()
                .Where(a => a.node_id == node_id && a.instance_id == instance_id && a.user_id == user_id && a.is_complete == 1)
                .FirstAsync();
            if (track_info != null)
            {
                return true;
            }

            return false;
        }

        public async Task<bool> IsApprovalCompleted(int user_id, int node_id, int instance_id)
        {
            var track_list = await _db.Queryable<jj_flow_instance_emp>()
                .Where(a => a.node_id == node_id && a.instance_id == instance_id && a.user_id != user_id && a.is_complete == 0)
                .ToListAsync();

            return track_list.Count == 0 ? true : false;
        }

        public async Task<List<jj_flow_instance_emp>> GetFormerlyNodeApprover(int instance_id, int sequence)
        {
            return await _db.Queryable<jj_flow_instance_emp, jj_node_reference>((a, b) =>
                    new JoinQueryInfos(JoinType.Left, a.instance_id == b.flow_instance_id && a.node_id == b.node_id))
                .Where(a => a.instance_id == instance_id && a.is_complete == 1)
                .Where((a, b) => b.sequence < sequence)
                .Select("a.*")
                .ToListAsync();
        }

        public async Task<List<jj_flow_instance_emp>> GetFutureNodeApprover(int instance_id, int sequence)
        {
            return await _db.Queryable<jj_flow_instance_emp, jj_node_reference>((a, b) =>
                    new JoinQueryInfos(JoinType.Left, a.instance_id == b.flow_instance_id && a.node_id == b.node_id))
                .Where(a => a.instance_id == instance_id && a.is_complete == 0)
                .Where((a, b) => b.sequence > sequence)
                .Select("a.*")
                .ToListAsync();
        }

        public async Task<jj_flow_instance_emp> GetUserInstanceEmp(int user_id, int node_id, int instance_id)
        {
            return await _db.Queryable<jj_flow_instance_emp>().Where(a => a.user_id == user_id && a.node_id == node_id && a.instance_id == instance_id).FirstAsync();
        }

        public async Task<List<jj_flow_instance_emp>> GetInstanceEmpListByUserId(string modular_key, int user_id)
        {
            var instance_user_list = await _db.Queryable<jj_flow_instance_emp, jj_node_reference, jj_flow_instance>(
                    (a, b, c) =>
                        new JoinQueryInfos(
                            JoinType.Left, a.instance_id == b.flow_instance_id && a.node_id == b.node_id,
                            JoinType.Inner, a.instance_id == c.id && c.status == "0" && c.modular_key == modular_key
                        ))
                .Where(a => a.user_id == user_id && a.is_complete == 0)
                .ToListAsync();
            return instance_user_list;
        }

        public async Task<List<jj_flow_instance_emp>> GetInstanceEmpByBIdAndNId(string biz_order_id, string modular_key)
        {
            var result = new List<jj_flow_instance_emp>();
            var model = await _db.Queryable<jj_flow_instance>()
                .Where(x => x.biz_order_id == biz_order_id && x.modular_key == modular_key && x.status != "30")
                .OrderBy(x => x.id, OrderByType.Desc)
                .FirstAsync();
            if (model != null)
            {
                var jj_node = await _db.Queryable<jj_node>().FirstAsync(x => x.id == model.current_node_id);
                if (jj_node != null)
                {
                    var node_reference = await _db.Queryable<jj_node_reference>().FirstAsync(x => x.flow_instance_id == model.id && x.node_id == model.current_node_id);
                    var sequence = node_reference != null ? node_reference.sequence + 1 : -1;
                    var next_node_id = (await _db.Queryable<jj_node_reference>().FirstAsync(x => x.flow_instance_id == model.id && x.sequence == sequence))?.node_id;
                    if (next_node_id != null)
                    {
                        var jjFlowInstanceEmp = await _db.Queryable<jj_flow_instance_emp>()
                            .Where(m => m.instance_id == model.id && m.node_id == next_node_id && m.is_complete == 0)
                            .OrderBy(m => m.id)
                            .FirstAsync();
                        result.Add(jjFlowInstanceEmp);
                    }
                }
            }

            return result;
        }

        public async Task<List<jj_flow_instance_emp>> GetListByTemplateIdAndInstanceId(int instanceId, int template_id)
        {
            return await _db.Queryable<jj_flow_instance_emp>()
                .Where(m => m.instance_id == instanceId && m.template_id == template_id)
                .ToListAsync();
        }

        public async Task<bool> GetUserPower(string system_key, string big_modular_key, string modular_key, int user_id)
        {
            var modularKeys = modular_key.Split(',');
            var instance_user_list = await _db.Queryable<jj_flow_instance_emp, jj_flow_instance>(
                    (a, b) => new JoinQueryInfos(
                        JoinType.Inner, a.instance_id == b.id && modularKeys.Contains(b.modular_key)
                    ))
                .Where(a => a.user_id == user_id)
                .ToListAsync();
            return instance_user_list.Count > 0;
        }

        public async Task<bool> ChangeApprover(int handoverUserId, int userId, List<int> ids)
        {
            var updateable = _db.Updateable<jj_flow_instance_emp>()
                .SetColumns(m => new jj_flow_instance_emp
                {
                    user_id = handoverUserId,
                    update_user_id = _initUserInfo.InitDefaultUserInfo().Id,
                    update_time = DateTime.Now
                });

            if (userId > 0)
            {
                updateable = updateable.Where(m => m.user_id == userId && m.is_complete == 0 && ids.Contains(m.id));
            }
            else
            {
                updateable = updateable.Where(m => m.is_complete == 0 && ids.Contains(m.id));
            }

            return await updateable.ExecuteCommandHasChangeAsync();
        }

        public async Task<List<string>> GetUserBizOrderId(string modular_key, int user_id)
        {
            var sql = @"SELECT t2.biz_order_id
            FROM jj_flow_instance_emp t1
                INNER JOIN jj_flow_instance t2 ON t1.instance_id=t2.id
            INNER JOIN (
                SELECT distinct f.flow_instance_id,f.node_id
                FROM jj_flow_instance_emp a
            INNER JOIN jj_flow_instance c ON a.instance_id=c.id
            INNER JOIN jj_node_reference b ON a.instance_id=b.flow_instance_id AND c.current_node_id=b.node_id
            INNER JOIN jj_node_reference f ON f.flow_instance_id=b.flow_instance_id AND f.sequence = b.sequence + 1
            WHERE
            find_in_set(c.modular_key, @modular) AND c.`status` = '0' AND a.is_complete=0 AND a.user_id = @user_id
                ) t3 ON t1.instance_id = t3.flow_instance_id AND t1.node_id = t3.node_id
            WHERE
            find_in_set(t2.modular_key, @modular) AND t2.`status` = '0' AND t1.is_complete=0 AND t1.user_id = @user_id";

            var parameters = new List<SugarParameter>
            {
                new SugarParameter("@modular", modular_key),
                new SugarParameter("@user_id", user_id)
            };

            var instance_ids = await _db.Ado.SqlQueryAsync<string>(sql, parameters);
            return instance_ids;
        }


        /// <summary>
        /// 首页-待办 汇总页的数据
        /// </summary>
        /// <param name="currentUserId">当前用户的id</param>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageListSearchResultVM<MyTodoListOverview>> GetTodoListForCurrentUser(int currentUserId, SearchConditionBaseInput input)
        {
            var query = _db.Queryable<
                    jj_flow_instance_emp,
                    jj_flow_instance,
                    jj_flow_template, jj_flow_mapping_tbl, erpa_function_tbl>(
                    (t1, t2, template, mapping, erpa_function_tbl) => new JoinQueryInfos(
                        JoinType.Inner, t1.instance_id == t2.id,
                        JoinType.Inner, t2.template_id == template.id,
                        JoinType.Inner, template.id == mapping.template_id,
                        JoinType.Inner, template.page_id == erpa_function_tbl.id && erpa_function_tbl.delete_flag == "0"
                    ))
                .AddJoinInfo(@$"(
            SELECT distinct f.flow_instance_id, f.node_id, g.node_name as next_node_name -- 这个其实是当前节点的下一节点
                FROM
            jj_flow_instance_emp a
            INNER JOIN jj_flow_instance c ON a.instance_id = c.id
            INNER JOIN jj_node_reference b ON a.instance_id = b.flow_instance_id AND c.current_node_id = b.node_id
            INNER JOIN jj_node_reference f ON f.flow_instance_id = b.flow_instance_id AND f.sequence = b.sequence + 1
            INNER JOIN jj_node g ON g.id = f.node_id
            WHERE
            c.`status` = '0' AND a.is_complete = 0  AND a.user_id = {currentUserId}
            )", "t3", "t1.instance_id = t3.flow_instance_id AND t1.node_id = t3.node_id", JoinType.Inner)
                .Where((t1, t2) => t2.status == "0" && t1.is_complete == 0)
                .Where(t1 => t1.user_id == currentUserId)
                .GroupBy((t1, t2, template, mapping, erpa_function_tbl) => new
                {
                    mapping.todo_list_type,
                    erpa_function_tbl.function_name,
                    erpa_function_tbl.name,
                    erpa_function_tbl.path,
                    template.page_parameter_key
                })
                .Select<MyTodoListOverview>(
                    @" mapping.todo_list_type,
            erpa_function_tbl.function_name as page_name,
            erpa_function_tbl.name as route_name,
            erpa_function_tbl.path as page_route,
            template.page_parameter_key,count(*) as count");

            var paginatedResult = await query.ToPageListAsync(input, new DynamicTableHeaderInput()
            {
                MethodName = "ERPAssistant.Api.Controllers.JJFlowController.GetMyTodoList",
                UserId = currentUserId
            });
            return paginatedResult;


            //            var sql = $@"select 
            // t.todo_list_type,t.page_name,t.page_route,t.page_parameter_key,count(*) as count  from
            //(SELECT mapping.todo_list_type,erpa_function_tbl.function_name as page_name,template.page_route,
            //        template.page_parameter_key
            //            FROM jj_flow_instance_emp t1
            //                INNER JOIN jj_flow_instance t2 ON t1.instance_id=t2.id
            //                INNER JOIN jj_flow_template template on t2.template_id = template.id
            //                INNER JOIN jj_flow_mapping_tbl mapping on template.id = mapping.template_id
            //                INNER JOIN erpa_function_tbl  on template.page_route = erpa_function_tbl.path and erpa_function_tbl.delete_flag = '0'
            //            INNER JOIN (
            //                SELECT distinct f.flow_instance_id,f.node_id
            //                FROM 
            //                jj_flow_instance_emp a
            //                INNER JOIN jj_flow_instance c ON a.instance_id=c.id
            //                INNER JOIN jj_node_reference b ON a.instance_id=b.flow_instance_id AND c.current_node_id=b.node_id
            //                INNER JOIN jj_node_reference f ON f.flow_instance_id=b.flow_instance_id AND f.sequence = b.sequence + 1
            //            WHERE
            //             c.`status` = '0' AND a.is_complete=0  AND a.user_id = {currentUserId}
            //                ) t3 ON t1.instance_id = t3.flow_instance_id AND t1.node_id = t3.node_id
            //            WHERE
            //            t2.`status` = '0' AND t1.is_complete=0  AND t1.user_id = {currentUserId}
            //           ) t 
            //          group by t.todo_list_type,t.page_name,t.page_route,t.page_parameter_key
            //       ";

            //            var parameters = new List<SugarParameter>
            //            {
            //                new SugarParameter("@user_id", currentUserId)
            //            };

            //            var res = await _db.Ado.SqlQueryAsync<MyTodoListOverview>(sql, parameters);
        }


        public async Task<int> GetMyTodoListCount(int currentUserId)
        {
            var sql = @"
SELECT count(*)
FROM `jj_flow_instance_emp` t1
         Inner JOIN `jj_flow_instance` t2 ON (`t1`.`instance_id` = `t2`.`id`)
         Inner JOIN `jj_flow_template` template ON (`t2`.`template_id` = `template`.`id`)
         Inner JOIN `jj_flow_mapping_tbl` mapping ON (`template`.`id` = `mapping`.`template_id`)
         Inner JOIN `erpa_function_tbl` erpa_function_tbl ON ((`template`.`page_id` = `erpa_function_tbl`.`id`) AND
                                                              (`erpa_function_tbl`.`delete_flag` = '0'))
         Inner JOIN `erpa_user_tbl` erpa_user_tbl ON (`t2`.`start_user` = `erpa_user_tbl`.`id`)
         Inner JOIN (SELECT distinct f.flow_instance_id, f.node_id, g.node_name as next_node_name -- 这个其实是当前节点的下一节点
                     FROM jj_flow_instance_emp a
                              INNER JOIN jj_flow_instance c ON a.instance_id = c.id
                              INNER JOIN jj_node_reference b
                                         ON a.instance_id = b.flow_instance_id AND c.current_node_id = b.node_id
                              INNER JOIN jj_node_reference f
                                         ON f.flow_instance_id = b.flow_instance_id AND f.sequence = b.sequence + 1
                              INNER JOIN jj_node g ON g.id = f.node_id
                     WHERE c.`status` = '0'
                       AND a.is_complete = 0
                       AND a.user_id = @user_id) t3 ON t1.instance_id = t3.flow_instance_id AND t1.node_id = t3.node_id
WHERE ((`t2`.`status` = '0') AND (`t1`.`is_complete` = 0))
  AND (`t1`.`user_id` = @user_id)
ORDER BY `t2`.`update_time` DESC;";

            var parameters = new List<SugarParameter>
            {
                new SugarParameter("@user_id", currentUserId)
            };
            var res = await _db.Ado.SqlQuerySingleAsync<int>(sql, parameters);
            return res;
        }


        /// <summary>
        /// 首页-待办-明细
        /// </summary>
        /// <param name="currentUserId">当前用户的id</param>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageListSearchResultVM<MyTodoListDetail>> GetTodoListDetailForCurrentUser(int currentUserId, GetToDoListDetailByTab input)
        {
            ISugarQueryable<MyTodoListDetail> query = null;
            switch (input.approval_type)
            {
                case ApprovalType.我提交的审批:
                    query = _db.Queryable<
                            jj_flow_instance,
                            jj_flow_template, jj_flow_mapping_tbl, erpa_function_tbl, erpa_user_tbl>(
                            (t2, template, mapping, erpa_function_tbl, erpa_user_tbl) => new JoinQueryInfos(
                                JoinType.Inner, t2.template_id == template.id,
                                JoinType.Inner, template.id == mapping.template_id,
                                JoinType.Inner,
                                template.detail_page_id == erpa_function_tbl.id && erpa_function_tbl.delete_flag == "0",
                                JoinType.Inner, t2.start_user == erpa_user_tbl.id
                            ))
                        .AddJoinInfo(@$"(
            SELECT distinct f.flow_instance_id,
                   f.node_id,
                   CONCAT('(',g.node_name,')',(select GROUP_CONCAT( u.user_name, '') from jj_flow_instance_emp emp  inner join erpa_user_tbl u on emp.user_id = u.id
                                                                 where  instance_id= f.flow_instance_id and node_id = f.node_id
                                                                 )) as next_node_name -- 这个其实是当前节点的下一节点
            FROM
            jj_flow_instance c 
            INNER JOIN jj_node_reference b ON c.id = b.flow_instance_id AND c.current_node_id = b.node_id
            INNER JOIN jj_node_reference f ON f.flow_instance_id = b.flow_instance_id AND f.sequence = b.sequence + 1
            INNER JOIN jj_node g ON g.id = f.node_id   
            WHERE
            c.start_user = {currentUserId} and c.status = '0'
            )", "t3", "t2.id = t3.flow_instance_id", JoinType.Inner)
                        .Where(t2 => t2.start_user == currentUserId
                                     && t2.status == "0" // 只显示审批中的
                        )
                        .OrderBy((t2, template, mapping, erpa_function_tbl, erpa_user_tbl) => t2.update_time,
                            OrderByType.Desc)
                        .Select<MyTodoListDetail>(
                            @" erpa_function_tbl.path as detail_page_route,template.detail_page_parameter_key,
mapping.modular_name,
erpa_user_tbl.user_name as start_user_name,
t2 .update_time, -- 审批人提交的时间/最近一个审批人审批的时间
t2 .biz_order_id,
t3.next_node_name as node_name"); // node.node_name是节点名称
                    break;
                // 默认是待我审批
                default:
                    query = _db.Queryable<
                            jj_flow_instance_emp,
                            jj_flow_instance,
                            jj_flow_template, jj_flow_mapping_tbl, erpa_function_tbl, erpa_user_tbl>(
                            (t1, t2, template, mapping, erpa_function_tbl, erpa_user_tbl) => new JoinQueryInfos(
                                JoinType.Inner, t1.instance_id == t2.id,
                                JoinType.Inner, t2.template_id == template.id,
                                JoinType.Inner, template.id == mapping.template_id,
                                JoinType.Inner,
                                template.detail_page_id == erpa_function_tbl.id && erpa_function_tbl.delete_flag == "0",
                                JoinType.Inner, t2.start_user == erpa_user_tbl.id
                            ))
                        .AddJoinInfo(@$"(
            SELECT distinct f.flow_instance_id, f.node_id,CONCAT('(',g.node_name,')',(select GROUP_CONCAT( u.user_name, '') from jj_flow_instance_emp emp  inner join erpa_user_tbl u on emp.user_id = u.id
                                                                 where  instance_id= f.flow_instance_id and node_id = f.node_id
                                                                 )) as next_node_name -- 这个其实是当前节点的下一节点
                FROM
            jj_flow_instance_emp a
            INNER JOIN jj_flow_instance c ON a.instance_id = c.id
            INNER JOIN jj_node_reference b ON a.instance_id = b.flow_instance_id AND c.current_node_id = b.node_id
            INNER JOIN jj_node_reference f ON f.flow_instance_id = b.flow_instance_id AND f.sequence = b.sequence + 1
            INNER JOIN jj_node g ON g.id = f.node_id   
            WHERE
            c.`status` = '0' AND a.is_complete = 0  AND a.user_id = {currentUserId}
            )", "t3", "t1.instance_id = t3.flow_instance_id AND t1.node_id = t3.node_id", JoinType.Inner)
                        .Where((t1, t2) => t2.status == "0" && t1.is_complete == 0)
                        .Where(t1 => t1.user_id == currentUserId)
                        .OrderBy((t1, t2, template, mapping, erpa_function_tbl, erpa_user_tbl) => t2.update_time,
                            OrderByType.Desc)
                        .Select<MyTodoListDetail>(
                            @" erpa_function_tbl.path as detail_page_route,template.detail_page_parameter_key,
mapping.modular_name,
erpa_user_tbl.user_name as start_user_name,
t2 .update_time, -- 审批人提交的时间/最近一个审批人审批的时间
t2 .biz_order_id,
t3.next_node_name as node_name"); // node.node_name是节点名称

                    break;
            }

            var paginatedResult = await query.ToPageListAsync(input, new DynamicTableHeaderInput
            {
                MethodName = "ERPAssistant.Api.Controllers.JJFlowController.GetMyTodoListDetail",
                UserId = currentUserId
            });


            // 路由/url替换的规则

            // 第一种情况（普通替换）：backUrl=purchaseOrderList&flag=3&id={0}
            // detail_page_parameter_key中是类似：backUrl=purchaseOrderList&flag=3&id={0}&parent_t=1661491366551&t=1661569516605的数据
            // 其中id={0}是需要替换为detail.biz_order_id，和c# 字符串的插值规则一样


            // 其中id=[celeb_promote_tbl.order_id.package_id] 表示 需要去数据库里 表名为celeb_promote_tbl查找字段order_id且条件为package_id=biz_order_id(biz_order_id在res中有)，假设查找得到的order_id等于12，
            // 则需要用12替换掉[celeb_promote_tbl.order_id],最后变为backUrl=purchaseOrderList&flag=3&id=12
            // 第二种情况（特殊替换）：backUrl=purchaseOrderList&flag=3&id=34177&parent_t=1661491366551&t=1661569516605


            // 1 先处理特殊替换
            var special = paginatedResult.data_list.Where(a =>
                    a.detail_page_parameter_key.Contains("[") && a.detail_page_parameter_key.Contains("]"))
                .Select(a => new BusinessNumberConvert
                {
                    BizOrderId = a.biz_order_id,
                    RouteKey = a.detail_page_route,
                    Start = a.detail_page_parameter_key.IndexOf('['),
                    End = a.detail_page_parameter_key.IndexOf(']'),
                    // 表名
                    Table = a.detail_page_parameter_key.Substring(a.detail_page_parameter_key.IndexOf('[') + 1,
                            a.detail_page_parameter_key.IndexOf(']') - 1 - a.detail_page_parameter_key.IndexOf('['))
                        .Split('.')[0],
                    // 列名
                    FieldName = a.detail_page_parameter_key.Substring(a.detail_page_parameter_key.IndexOf('[') + 1,
                            a.detail_page_parameter_key.IndexOf(']') - 1 - a.detail_page_parameter_key.IndexOf('['))
                        .Split('.')[1],
                    // 条件列
                    ConditionFieldName = a.detail_page_parameter_key
                        .Substring(a.detail_page_parameter_key.IndexOf('[') + 1,
                            a.detail_page_parameter_key.IndexOf(']') - 1 - a.detail_page_parameter_key.IndexOf('['))
                        .Split('.')[2]
                }).ToList();

            if (special.Any())
            {
                // 分组
                var tableAndField = special.GroupBy(x => new { x.Table, x.FieldName, x.ConditionFieldName });
                // 有几组
                var list = new List<RouteConvert>();
                foreach (var grouping in tableAndField)
                {
                    // 拿出某一组的数据
                    var condition = special.Where(x =>
                        x.Table == grouping.Key.Table &&
                        x.FieldName == grouping.Key.FieldName &&
                        x.ConditionFieldName == grouping.Key.ConditionFieldName).ToList();
                    // 查出组内的结果

                    // 以id=[celeb_promote_tbl.order_id.package_id]为例，select order_id,xx from celeb_promote_tbl where package_id in (xx,xx,xx)
                    // {condition.First().ConditionFieldName} 就是 package_id
                    // grouping.Key.FieldName 就是order_id
                    var sqlString =
                        $"select {grouping.Key.FieldName} as {nameof(RouteConvert.ConvertedId)}," +
                        $"{condition.First().ConditionFieldName} as {nameof(RouteConvert.BizOrderId)}" +
                        $" from {grouping.Key.Table} where {condition.First().ConditionFieldName} in ('{string.Join("','", condition.Select(x => x.BizOrderId))}')" +
                        $" group by {grouping.Key.FieldName},{condition.First().ConditionFieldName}";
                    var result = await _db.Ado.SqlQueryAsync<RouteConvert>(sqlString);
                    //把结果收集起来
                    list.AddRange(result);
                }

                // 因为一定可以检索得到，所以直接使用inner join
                var join = special.Join(list, s => s.BizOrderId, l => l.BizOrderId, (s, l) => new
                {
                    s.Table,
                    s.FieldName,
                    s.ConditionFieldName,
                    s.BizOrderId,
                    l.ConvertedId
                }).ToList();

                paginatedResult.data_list.ForEach(detail =>
                {
                    // 这里同时处理 特殊情况和普通情况
                    var record = join.FirstOrDefault(x => x.BizOrderId == detail.biz_order_id);
                    if (record == null)
                        detail.detail_page_parameter_key =
                            string.Format(detail.detail_page_parameter_key, detail.biz_order_id);
                    else
                        detail.detail_page_parameter_key = detail.detail_page_parameter_key.Replace(
                            $"[{record.Table}.{record.FieldName}.{record.ConditionFieldName}]", record.ConvertedId);
                });

                return paginatedResult;
            }


            // 仅仅有普通转换的情况
            paginatedResult.data_list.ForEach(detail =>
            {
                detail.detail_page_parameter_key =
                    string.Format(detail.detail_page_parameter_key, detail.biz_order_id);
            });

            return paginatedResult;
        }

        public async Task<IReadOnlyList<SearchByUrlResponse>> SearchByUrl(string url)
        {
            var query = _db.Queryable<erpa_function_tbl>()
                .Where(x => x.delete_flag == "0" && (x.supplier_flag == "2" || x.supplier_flag == "4"));
            if (!string.IsNullOrWhiteSpace(url))
            {
                query = query.Where(x => x.path.Contains(url)); // 这里x.path和x.router_url是一样的数据
            }

            return await query.Select(x => new SearchByUrlResponse
            {
                id = x.id,
                url = x.path
            }).Take(20).ToListAsync();
        }


        public async Task<bool> RecordReset(int instanceId, List<int> nodeIds)
        {
            return await _db.Updateable<jj_flow_instance_emp>()
                .SetColumns(m => m.is_complete == 0)
                .SetColumns(m => m.update_user_id == -1)
                .SetColumns(m => m.update_time == DateTime.Now)
                .Where(m => m.instance_id == instanceId && m.is_complete == 1 && nodeIds.Contains(m.node_id))
                .ExecuteCommandHasChangeAsync();
        }

        public async Task<int> DeleteAsync(Expression<Func<jj_flow_instance_emp,bool>> expression) => await _db.Deleteable<jj_flow_instance_emp>().Where(expression).ExecuteCommandAsync();
    }
}